#!/bin/bash

# 可安装 mysql 5.7 和 8.0 
# 可单独初始化

# 初始化新实例
mysql_init=0 # 1 初始化mysql/ 0 新装mysql  # 初始化适用于已安装 mysql 的情况 想新启动端口
mysqld_command=$(which  mysqld 2>/dev/null)
# 安装新实例
mysql_package_path="/tmp/mysql-5.7.44-linux-glibc2.12-x86_64.tar.gz"  # MySQL 压缩包路径
mysql_base_dir="/usr/local"  # MySQL 安装路径
mysql_data_dir="/data"  # MySQL datadir 路径
mysql_port=3306
mysql_innodb_buffer_pool_size=1   # mysql内存空间 单位:GB 默认当前可用内存的 70% 
mysql_gtid='ON'  # 是否开启 gtid: ON/OFF
mysql_semi_sync='OFF'  # 是否开启半同步复制: ON/OFF


# mysql下载链接
# curl -L -o mysql-5.7.44-linux-glibc2.12-x86_64.tar.gz https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.44-linux-glibc2.12-x86_64.tar.gz
# curl -L -o mysql-8.0.35-linux-glibc2.17-x86_64.tar.xz https://downloads.mysql.com/archives/get/p/23/file/mysql-8.0.35-linux-glibc2.17-x86_64.tar.xz

if [ "$mysql_innodb_buffer_pool_size" != '\s*' ];then
    innodb_buffer_pool_instances1="${mysql_innodb_buffer_pool_size}"
    mysql_innodb_buffer_pool_size="${mysql_innodb_buffer_pool_size}G"

else
    mysql_buffer_pool=$(awk '/MemAvailable/ { avail=$2/1024/1024*0.7; printf "%d\n", (avail < 1 ? 1 : avail) }' /proc/meminfo)
    innodb_buffer_pool_instances1="${mysql_buffer_pool}"
    mysql_innodb_buffer_pool_size="${mysql_buffer_pool}G"

fi 

mysql_base_dir="${mysql_base_dir%/}" 
mysql_data_dir="${mysql_data_dir%/}" 


if [ $mysql_init -ne 1  ];then
  # 检查MySQL 压缩包
  if [ ! -f "$mysql_package_path" ]; then
    echo "MySQL 压缩包不存在"
    exit 1
  fi
fi


if [ -f "/etc/my$mysql_port.cnf" ];then
  echo "/etc/my$mysql_port.cnf 已存在"
  exit 1
fi


ss -lntp|grep mysqld|awk '{print $4}'|grep "$mysql_port" >/dev/null
if [ $? -eq 0 ];then
   echo "mysql $mysql_port 正在运行 请确认"
   exit
fi

if [ $mysql_init -ne 1  ];then
  ping -c 1 baidu.com  >/dev/null
  if [ $? -eq 0 ]; then
    echo "互联网连接正常"
  else
    echo "互联网连接不通如内网运行请修改 ping 地址"
    exit 1
  fi
fi


cat /etc/redhat-release |grep 'CentOS'|grep '7.[0-9].' > /dev/null
if  [ $? -ne 0 ];then
  echo "非 centos 7 版本 本脚本仅适用于 centos 7.*"
  exit 1
fi

if [ $mysql_init -ne 1  ];then
  cat /etc/yum.repos.d/CentOS-Base.repo|grep aliyun >/dev/null
  if [ $? -ne 0 ]; then
    curl -o /etc/yum.repos.d/CentOS-Base.repo https://mirrors.aliyun.com/repo/Centos-7.repo
    yum clean all
  fi
fi



if [ $mysql_init -eq 0 ];then
#mysql_version=$(ls  "$mysql_base_dir/mysql/tar/mysql\-[58]\.[5670]\.[0-9]+\-linux.*x86_64.tar.[xg]z"|egrep  "mysql\-[58]\.[45670]\.[0-9]+\-linux.*x86_64"|awk -F'-' '{print $2}' |awk -F. '{print $1$2}')
mysql_version=$(ls $mysql_package_path|egrep  "mysql\-[589]\.[456702]\.[0-9]+\-linux.*x86_64.tar.[xg]z"|awk -F'-' '{print $2}' |awk -F. '{print $1$2}')

elif [ $mysql_init -eq 1 ];then
mysql_version=$($mysqld_command -V|awk '{print $3}'|awk -F. '{print $1$2}')
fi

if [ $mysql_init -ne 1  ];then
  if [[ -d "$mysql_base_dir/mysql/mysql${mysql_version}" ]];then
    echo "$mysql_base_dir/mysql/mysql${mysql_version} 安装路径已存在 请确认路径内容 删除目录后执行脚本"
    exit 1
  fi
fi

if [[ -d "$mysql_data_dir/mydata/${mysql_port}"  ]];then
  echo "$mysql_data_dir/mydata/${mysql_port} 数据路径已存在 请确认路径内容 删除目录后执行脚本"
  exit 1
fi


# 检查是否为 root 用户运行
if [ $(whoami) != "root" ]; then
  echo "请使用 root 用户运行此脚本！"
  exit 1
fi

if [ $mysql_init -ne 1  ];then
  # 创建标准目录结构
  mkdir -p $mysql_base_dir/mysql/{tools,tar} 
fi
mkdir -p $mysql_data_dir/mydata/$mysql_port/{binlog,data,etc,lock,log,pid,socket,tmp,script}

echo '创建目录已完成'

if [ $mysql_init -ne 1  ];then
  echo "安装包移至 $mysql_base_dir/mysql/tar"
  
  mv $mysql_package_path  $mysql_base_dir/mysql/tar
  if [ $? -ne 0 ];then 
    echo '移动失败'
    exit 1
  fi
  
  
  echo '安装包解压中···'
  cd $mysql_base_dir/mysql/tar
  tar xf $mysql_base_dir/mysql/tar/mysql-$(echo "$mysql_version" | sed 's/\(.\)\(.*\)/\1.\2/').[0-9]*-linux*-x86_64.tar.[xg]z
  
  if [ $? -ne 0 ];then 
    echo '安装包解压失败'
    exit 1
  fi
  
  
  mv $mysql_base_dir/mysql/tar/mysql-$(echo "$mysql_version" | sed 's/\(.\)\(.*\)/\1.\2/').[0-9]*-linux*-x86_64  $mysql_base_dir/mysql/mysql${mysql_version}
  if [ $? -ne 0 ];then 
    echo '移动失败'
    exit 1
  fi
fi


if ! id mysql &>/dev/null; then
  groupadd  mysql
  useradd mysql -g mysql
fi

echo 'mysql 用户已创建'

if [ $mysql_init -ne 1  ];then
echo '配置环境变量'
  
# 创建环境变量配置文件
cat >/etc/profile.d/mysql_${mysql_port}.sh<<ENDD
export PATH=${mysql_base_dir}/mysql/mysql${mysql_version}/bin:\$PATH
ENDD

if [ $? -ne 0 ];then 
  echo '配置环境配置失败'
  exit 1
fi

# 刷新环境变量
source /etc/profile.d/mysql_${mysql_port}.sh
  
fi


# 创建 error 文件
touch ${mysql_data_dir}/mydata/$mysql_port/log/mysql-error.log
if [ $? -ne 0 ];then 
  echo '创建 error 文件失败'
  exit 1
fi

# 创建配置文件
touch  ${mysql_data_dir}/mydata/$mysql_port/etc/my.cnf
if [ $? -ne 0 ];then 
  echo '创建配置文件失败'
  exit 1
fi

ln -s ${mysql_data_dir}/mydata/$mysql_port/etc/my.cnf  /etc/my$mysql_port.cnf
if [ $? -ne 0 ];then 
  echo '配置文件软连接失败'
  exit 1
fi


if [ $mysql_init -ne 1  ];then
  echo '安装依赖中···'
  
  yum install   -y  net-tools lrzsz wget  vim  libaio-devel ntpdate  epel-release \
  tree  libaio-devel lsof sysstat  bash-completion  bash-completion-extras jemalloc 
  if [ $? -ne 0 ];then 
    echo '安装依赖失败'
    exit 1
  fi
fi

# 授权 MySQL 目录
chown -R mysql:mysql ${mysql_data_dir}/mydata/${mysql_port}
chown -R mysql:mysql ${mysql_base_dir}/mysql/mysql${mysql_version}


echo 'MySQL 正在初始化···'

if [ $mysql_init -eq 0 ];then
  mysql_command="${mysql_base_dir}/mysql/mysql${mysql_version}/bin/mysqld"

elif [ $mysql_init -eq 1 ];then
  mysql_command=$mysqld_command
fi



if [ $mysql_init -eq 0 ];then
  # 初始化 MySQL
  $mysql_command --user=mysql --initialize-insecure --basedir=$mysql_base_dir/mysql/mysql${mysql_version} --datadir=$mysql_data_dir/mydata/${mysql_port}/data --innodb_data_file_path=ibdata1:1024M:autoextend --lower_case_table_names=1
  if [ $? -ne 0 ];then 
    echo 'mysql 初始化失败'
    exit 1
  fi
elif [ $mysql_init -eq 1 ];then
  $mysqld_command  --user=mysql --initialize-insecure --basedir=$mysql_base_dir/mysql/mysql${mysql_version} --datadir=$mysql_data_dir/mydata/${mysql_port}/data --innodb_data_file_path=ibdata1:1024M:autoextend --lower_case_table_names=1
  if [ $? -ne 0 ];then 
    echo 'mysql 初始化失败'
    exit 1
  fi
else
  echo '\$mysql_init 变量仅可配置为 0/1'

fi

cat >/usr/local/bin/${mysql_port}_mysql_login.sh<<ENDD
mysql -S $mysql_data_dir/mydata/${mysql_port}/socket/mysql.sock -A -p

ENDD

chmod 700  /usr/local/bin/${mysql_port}_mysql_login.sh


# 创建 systemd 服务配置文件
cat  >/etc/systemd/system/mysqld${mysql_port}.service<<ENDD
[Unit]
Description=MySQL ${mysql_port}  Server
After=network.target
After=syslog.target

[Install]
WantedBy=multi-user.target

[Service]
User=mysql
Group=mysql
ExecStart=${mysql_command}  --defaults-file=/etc/my$mysql_port.cnf
LimitNOFILE = 65536
LimitNPROC = 65535
# 重启条件
Restart=on-failure
# 退出码为1不重启 即正常shutdown
RestartPreventExitStatus=1
# 重启条件满足后等多久自动重启(秒)
RestartSec=10
# 五分钟内只能重启两次，第三次不重启了
StartLimitInterval=300
# 五分钟内只能重启两次，第三次不重启了
StartLimitBurst=2
# 服务启动的超时时间，单位秒
TimeoutStartSec=30
# 服务关闭的超时时间，单位秒
TimeoutStopSec=30
ENDD

systemctl daemon-reload

echo 'mysql 安装完成'
echo "请配置配置文件后启动!!!"
echo "#启动命令:"
echo "systemctl start mysqld${mysql_port}"
echo "#自启动命令:"
echo "systemctl enable mysqld${mysql_port}"
echo " "


if [ $mysql_version -eq 80 ];then
cat >/etc/my${mysql_port}.cnf<<ENDD
[client]
socket=$mysql_data_dir/mydata/$mysql_port/socket/mysql.sock
[mysql]
socket=$mysql_data_dir/mydata/$mysql_port/socket/mysql.sock
prompt='\u@\d>\_'
default-character-set=utf8mb4
no-auto-rehash
#show-warnings  #查看告警信息

[mysqld]
# GENERAL #
default_authentication_plugin  = mysql_native_password
mysqlx                         = OFF
mysqlx_port                    = ${mysql_port}0
mysqlx_socket                  = $mysql_data_dir/mydata/$mysql_port/socket/mysqlx.sock
admin_address                  = 127.0.0.1
admin_port                     = ${mysql_port}2
lower_case_table_names         = 1
character_set_server           = utf8mb4
collation_server               = utf8mb4_general_ci
user                           = mysql
default_storage_engine         = InnoDB
port                           = $mysql_port
socket                         = $mysql_data_dir/mydata/$mysql_port/socket/mysql.sock
pid-file                       = $mysql_data_dir/mydata/$mysql_port/pid/mysql.pid
log_timestamps                 = SYSTEM
default_time_zone              = +08:00
secure_file_priv               = ''
local_infile                   = ON
thread_stack                   = 512K
# MyISAM #
key_buffer_size                = 128M
myisam_sort_buffer_size        = 8M
read_rnd_buffer_size           = 4M

# SAFETY #
back_log                       = 1024
bind-address                   = 0.0.0.0
innodb_strict_mode             = 1
max_allowed_packet             = 32M
max_connect_errors             = 1000000
interactive_timeout            = 900
wait_timeout                   = 900
skip_grant_tables              = 0
skip_name_resolve              = 1
host_cache_size                = 0
sql_mode                       = NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
sysdate_is_now                 = 1
transaction_isolation          = READ-COMMITTED
explicit_defaults_for_timestamp = 1

# DATA STORAGE #
basedir                        = $mysql_base_dir/mysql/mysql${mysql_version}
datadir                        = $mysql_data_dir/mydata/$mysql_port/data
tmpdir                         = $mysql_data_dir/mydata/$mysql_port/tmp

# BINARY LOGGING #
#max_binlog_files              = 100
binlog_transaction_dependency_tracking = WRITESET
binlog_cache_size              = 1M
binlog_format                  = ROW
#binlog_expire_logs_seconds     = 7
max_binlog_size                = 1G
max_binlog_cache_size          = 4G
log_bin                        = $mysql_data_dir/mydata/$mysql_port/binlog/mysql-bin
log_bin_index                  = $mysql_data_dir/mydata/$mysql_port/binlog/mysql-bin.index
sync_binlog                    = 1

# CACHES AND LIMITS #
max_connections                = 2000
max_heap_table_size            = 128M
tmp_table_size                 = 128M
open_files_limit               = 65535
join_buffer_size               = 1M
read_buffer_size               = 4M
sort_buffer_size               = 1M
table_definition_cache         = 4096
table_open_cache               = 4000
thread_cache_size              = 1000

# INNODB #
innodb_stats_persistent_sample_pages = 500
lock_wait_timeout              = 3600
bulk_insert_buffer_size        = 64M
innodb_max_undo_log_size       = 4G
innodb_autoextend_increment    = 128M
innodb_buffer_pool_instances   = ${innodb_buffer_pool_instances1}
innodb_buffer_pool_size        = ${mysql_innodb_buffer_pool_size}
innodb_buffer_pool_load_at_startup  = ON
innodb_buffer_pool_dump_at_shutdown = ON
innodb_data_file_path          = ibdata1:1024M:autoextend
innodb_fast_shutdown           = 1
innodb_file_per_table          = 1
innodb_force_recovery          = 0
innodb_flush_log_at_trx_commit = 1
innodb_flush_method            = O_DIRECT
innodb_lock_wait_timeout       = 120
innodb_log_buffer_size         = 64M
innodb_log_file_size           = 1024M
innodb_log_files_in_group      = 4
innodb_open_files              = 10000
innodb_purge_threads           = 4
innodb_read_io_threads         = 8
innodb_write_io_threads        = 8
innodb_sync_spin_loops         = 30
innodb_thread_concurrency      = 24
innodb_print_all_deadlocks     = 1
innodb_monitor_enable = "module_innodb"
innodb_monitor_enable = "module_server"
innodb_monitor_enable = "module_dml"
innodb_monitor_enable = "module_ddl"
innodb_monitor_enable = "module_trx"
innodb_monitor_enable = "module_os"
innodb_monitor_enable = "module_purge"
innodb_monitor_enable = "module_log"
innodb_monitor_enable = "module_lock"
innodb_monitor_enable = "module_buffer"
innodb_monitor_enable = "module_index"
innodb_monitor_enable = "module_ibuf_system"
innodb_monitor_enable = "module_buffer_page"



# LOGGING #
general_log                    = 0
general_log_file               = $mysql_data_dir/mydata/$mysql_port/log/mysql-general.log
log_error                      = $mysql_data_dir/mydata/$mysql_port/log/mysql-error.log
log_throttle_queries_not_using_indexes = 60
min_examined_row_limit         = 100
log_slow_admin_statements      = 1
log_slow_slave_statements      = 1
log_error_verbosity            = 3
log_queries_not_using_indexes  = 1
long_query_time                = 1
slow_query_log                 = 1
slow_query_log_file            = $mysql_data_dir/mydata/$mysql_port/log/mysql-slow.log



# GTID
enforce_gtid_consistency       = ON
gtid_mode                      = $mysql_gtid
binlog_gtid_simple_recovery    = TRUE

# SEMI-SYNC
plugin-load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
rpl_semi_sync_master_enabled = $mysql_semi_sync
rpl_semi_sync_slave_enabled = $mysql_semi_sync
rpl_semi_sync_master_timeout = 1000
rpl_semi_sync_master_trace_level = 32
rpl_semi_sync_slave_trace_level = 32
rpl_semi_sync_master_wait_no_slave = ON

# REPLICATION #
log_replica_updates            = 1
read_only                      = 0
relay_log                      = $mysql_data_dir/mydata/$mysql_port/binlog/relay-bin
relay_log_index                = $mysql_data_dir/mydata/$mysql_port/binlog/relay-bin.index
server_id                      = ${mysql_port}$(shuf -i 1000-9999 -n 1)
replica_skip_errors            = OFF
skip_replica_start             = 0
replica_net_timeout            = 60
replica_compressed_protocol    = OFF   #5.6版本、5.7.21+、8.0.4+可以设置为ON

##parallel ##
replica_parallel_type                   = LOGICAL_CLOCK
replica_parallel_workers                = 20
slave_rows_search_algorithms            = 'INDEX_SCAN,HASH_SCAN'
binlog_group_commit_sync_delay          = 500
binlog_group_commit_sync_no_delay_count = 13
slave_preserve_commit_order             = 1
binlog_transaction_dependency_tracking  = writeset
transaction_write_set_extraction        = XXHASH64
slave_checkpoint_period                 = 2
binlog_checksum                         = CRC32
log_slave_updates                       = ON
master_info_repository                  = TABLE
relay_log_info_repository               = TABLE
relay_log_recovery                      = 1
ENDD

elif [ $mysql_version -eq 57 ];then

cat >/etc/my$mysql_port.cnf<<ENDD
[client]
socket=$mysql_data_dir/mydata/$mysql_port/socket/mysql.sock
[mysql]
socket=$mysql_data_dir/mydata/$mysql_port/socket/mysql.sock
prompt='\u@\d>\_'
default-character-set=utf8mb4
no-auto-rehash
#show-warnings  #查看告警信息

[mysqld]
# GENERAL #
lower_case_table_names         = 1
character_set_server           = utf8mb4
collation_server               = utf8mb4_general_ci
user                           = mysql
default_storage_engine         = InnoDB
port                           = $mysql_port
socket                         = $mysql_data_dir/mydata/$mysql_port/socket/mysql.sock
pid-file                       = $mysql_data_dir/mydata/$mysql_port/pid/mysql.pid
log_timestamps                 = SYSTEM
default_time_zone              = +08:00
secure_file_priv               = ''
local_infile                   = ON
thread_stack                   = 512K
# MyISAM #
key_buffer_size                = 128M
myisam_sort_buffer_size        = 8M
read_rnd_buffer_size           = 262144

# SAFETY #
back_log                       = 1024
bind-address                   = 0.0.0.0
innodb_strict_mode             = 1
max_allowed_packet             = 32M
max_connect_errors             = 1000000
interactive_timeout            = 900
wait_timeout                   = 900
skip_grant_tables              = 0
skip_name_resolve              = 1
host_cache_size                = 0
sql_mode                       = NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
sysdate_is_now                 = 1
transaction_isolation          = REPEATABLE-READ
explicit_defaults_for_timestamp = 1

# DATA STORAGE #
basedir                        = $mysql_base_dir/mysql/mysql${mysql_version}
datadir                        = $mysql_data_dir/mydata/$mysql_port/data
tmpdir                         = $mysql_data_dir/mydata/$mysql_port/tmp

# BINARY LOGGING #
#max_binlog_files              = 100
binlog_cache_size              = 1M
binlog_format                  = ROW
expire_logs_days               = 15
max_binlog_size                = 512M
max_binlog_cache_size          = 4G
log_bin                        = $mysql_data_dir/mydata/$mysql_port/binlog/mysql-bin
log_bin_index                  = $mysql_data_dir/mydata/$mysql_port/binlog/mysql-bin.index
sync_binlog                    = 1
innodb_support_xa              = 1
master-info-repository         = TABLE

# REPLICATION #
log_slave_updates              = 1
read_only                      = 0
relay_log                      = $mysql_data_dir/mydata/$mysql_port/binlog/relay-bin
relay_log_index                = $mysql_data_dir/mydata/$mysql_port/binlog/relay-bin.index
server_id                      = ${mysql_port}$(shuf -i 1000-9999 -n 1)
slave_skip_errors              = OFF
skip_slave_start               = 0
slave_net_timeout              = 60
relay-log-info-repository      = TABLE
slave_compressed_protocol      = OFF   #5.6版本、5.7.21+、8.0.4+可以设置为ON

# CACHES AND LIMITS #
max_connections                = 2000
max_heap_table_size            = 128M
tmp_table_size                 = 128M
open_files_limit               = 65535
query_cache_limit              = 1M
query_cache_min_res_unit       = 4096
query_cache_size               = 0
query_cache_type               = 0
join_buffer_size               = 1M
read_buffer_size               = 1M
sort_buffer_size               = 1M
table_definition_cache         = 4096
table_open_cache               = 4000
thread_cache_size              = 1000

# INNODB #
innodb_autoextend_increment    = 64
innodb_buffer_pool_instances   = ${innodb_buffer_pool_instances1}
innodb_buffer_pool_size        = ${mysql_innodb_buffer_pool_size}
innodb_buffer_pool_load_at_startup  = ON
innodb_buffer_pool_dump_at_shutdown = ON
innodb_data_file_path          = ibdata1:1024M:autoextend
innodb_fast_shutdown           = 1
innodb_file_format             = Barracuda
innodb_file_format_max         = Barracuda
innodb_file_per_table          = 1
innodb_force_recovery          = 0
innodb_flush_log_at_trx_commit = 1
innodb_flush_method            = O_DIRECT
innodb_lock_wait_timeout       = 120
innodb_log_buffer_size         = 64M
innodb_log_file_size           = 1024M
innodb_log_files_in_group      = 4
innodb_open_files              = 10000
innodb_purge_threads           = 4
innodb_read_io_threads         = 8
innodb_write_io_threads        = 8
innodb_sync_spin_loops         = 30
innodb_thread_concurrency      = 24
innodb_print_all_deadlocks     = 1
innodb_io_capacity             = 2000

# LOGGING #
general_log                    = 0
general_log_file               = $mysql_data_dir/mydata/$mysql_port/log/mysql-general.log
log_error                      = $mysql_data_dir/mydata/$mysql_port/log/mysql-error.log
log_queries_not_using_indexes  = 0
long_query_time                = 1
slow_query_log                 = 1
slow_query_log_file            = $mysql_data_dir/mydata/$mysql_port/log/mysql-slow.log

# GTID
enforce_gtid_consistency       = ON
gtid_mode                      = $mysql_gtid
binlog_gtid_simple_recovery    = TRUE

# SEMI-SYNC
plugin-load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
rpl_semi_sync_master_enabled = $mysql_semi_sync
rpl_semi_sync_slave_enabled = $mysql_semi_sync
rpl_semi_sync_master_timeout = 1000
rpl_semi_sync_master_trace_level = 32
rpl_semi_sync_slave_trace_level = 32
rpl_semi_sync_master_wait_no_slave = ON

##parallel 配置文件参数 ##
slave_rows_search_algorithms            = 'INDEX_SCAN,HASH_SCAN'
binlog_group_commit_sync_delay          = 500
binlog_group_commit_sync_no_delay_count = 13
slave_parallel_type                     = LOGICAL_CLOCK
slave_parallel_workers                  = 20
slave_preserve_commit_order             = 1
binlog_transaction_dependency_tracking  = writeset
transaction_write_set_extraction        = XXHASH64
slave_checkpoint_period                 = 2
binlog_checksum                         = CRC32
log_slave_updates                       = ON
master_info_repository                  = TABLE
relay_log_info_repository               = TABLE
relay_log_recovery                      = 1
ENDD

else
  echo  '版本信息有误 无法写入配置文件'
fi

